﻿#define INCLUDE_WEB_FUNCTIONS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Reflection;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.IO;
using DuocThaiBinh.Common;
using System.Web;
using System.Text.RegularExpressions;

namespace DuocThaiBinh.HtmlHelpers.ExportToExcel
{
    public class CreateExcelFile
    {
        /// <summary>
        /// The _date style id.
        /// </summary>
        private static UInt32Value _dateStyleId;

        /// <summary>
        /// The _default style id.
        /// </summary>
        private static UInt32Value _defaultStyleId;

        /// <summary>
        /// The _multiline style id.
        /// </summary>
        private static UInt32Value _multilineStyleId;

        /// <summary>
        /// The _curencel id.
        /// </summary>
        private static UInt32Value _curencelId;

        /// <summary>
        /// The _border id.
        /// </summary>
        private static UInt32Value _borderID;

        private static SharedStringTablePart _sharedStringTablePart1;

        public static MemoryStream GetFileTemplate(string filePath)
        {
            MemoryStream memoryStream = new MemoryStream();
            
            //var path=Path.GetDirectoryName(filePath);
            byte[] filebytes = File.ReadAllBytes(filePath);
            memoryStream.Write(filebytes, 0, filebytes.Length);
            return memoryStream;
        }

        public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(ListToDataTable(list));

            return CreateExcelDocument(ds, xlsxFilePath);
        }
        #region HELPER_FUNCTIONS

        public static DataTable ListToDataTable<T>(List<T> list)
        {
            DataTable dt = new DataTable();

            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));
            }
            foreach (T t in list)
            {
                DataRow row = dt.NewRow();
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    if (!IsNullableType(info.PropertyType))
                        row[info.Name] = info.GetValue(t, null);
                    else
                        row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
                }
                dt.Rows.Add(row);
            }
            return dt;
        }
        private static Type GetNullableType(Type t)
        {
            Type returnType = t;
            if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                returnType = Nullable.GetUnderlyingType(t);
            }
            return returnType;
        }
        private static bool IsNullableType(Type type)
        {
            return (type == typeof(string) ||
                    type.IsArray ||
                    (type.IsGenericType &&
                     type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));
        }

        public static MemoryStream CreateExcelDocument(DataTable dt, string fileName)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            var result = CreateExcelDocumentAsStream(ds, fileName);
            ds.Tables.Remove(dt);
            return result;
        }
        #endregion

#if INCLUDE_WEB_FUNCTIONS
        /// <summary>
        /// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
        /// </summary>
        /// <param name="dt">DataTable containing the data to be written to the Excel.</param>
        /// <param name="filename">The filename (without a path) to call the new Excel file.</param>
        /// <param name="Response">HttpResponse of the current page.</param>
        /// <returns>True if it was created succesfully, otherwise false.</returns>
        public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponseBase Response)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                CreateExcelDocumentAsStream(ds, filename);
                ds.Tables.Remove(dt);
                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }

        public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponseBase Response)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(ListToDataTable(list));
                CreateExcelDocumentAsStream(ds, filename);
                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }
        private static UInt32Value CreateBorder(Stylesheet styleSheet)
        {
            Border border = new Border();
            border.LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thin };
            border.RightBorder = new RightBorder() { Style = BorderStyleValues.Thin };
            border.TopBorder = new TopBorder() { Style = BorderStyleValues.Thin };
            border.BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin };
            styleSheet.Borders.Append(border);
            UInt32Value result = styleSheet.Borders.Count;
            styleSheet.Borders.Count++;
            return result;
        }
        public static void GenerateWorkbookStylesPart2Content(WorkbookStylesPart workbookStylesPart1)
        {
            Stylesheet stylesheet1 = workbookStylesPart1.Stylesheet;
            CellFormats cellFormats1 = stylesheet1.CellFormats;
            UInt32Value fontID = CreateFont(stylesheet1, "Arial", 9, false);
            CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)fontID, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyAlignment = true };
            Alignment alignment1 = new Alignment() { WrapText = true };
            cellFormat4.Append(alignment1);

            cellFormats1.Append(cellFormat4);
            _multilineStyleId = cellFormats1.Count;
            cellFormats1.Count++;
        }


        public static MemoryStream ExportWithTemplate(DataTable table, MemoryStream ms, string[] cellStyleIndex)
        {
            ms.Position = 0;

            using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, true))
            {
                WorkbookPart workbook = spreadsheet.WorkbookPart;
                WorksheetPart worksheet = workbook.WorksheetParts.Last();
                SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
                Stylesheet styleSheet = workbook.WorkbookStylesPart.Stylesheet;

                UInt32Value fontID = CreateFont(styleSheet, "Arial", 9, false);

                _borderID = CreateBorder(styleSheet);
                _dateStyleId = createCellFormat(styleSheet, fontID, null, UInt32Value.FromUInt32(2));
                _curencelId = createCellFormat(styleSheet, fontID, null, UInt32Value.FromUInt32(164));
                _defaultStyleId = createCellFormat(styleSheet, fontID, null, null);


                WorkbookStylesPart workbookStylesPart = workbook.WorkbookStylesPart;
                GenerateWorkbookStylesPart2Content(workbookStylesPart);
                _sharedStringTablePart1 = workbook.SharedStringTablePart;
                if (_sharedStringTablePart1 == null)
                {
                    _sharedStringTablePart1 = workbook.AddNewPart<SharedStringTablePart>("rId10");
                }

                if (_sharedStringTablePart1.SharedStringTable == null)
                    _sharedStringTablePart1.SharedStringTable = new SharedStringTable()
                    {
                        Count = (UInt32Value)0U,
                        UniqueCount = (UInt32Value)0U
                    };

                if (table != null)
                {
                    DataRow contentRow;
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        contentRow = table.Rows[i];
                        data.AppendChild(createContentRow(contentRow, i + 1, cellStyleIndex, worksheet.Worksheet));
                    }
                }
            }

            return ms;
        }

        private static Row createContentRow(
           DataRow dataRow,
           int rowIndex,
           string[] cellStyleIndex,
           Worksheet worksheet)
        {
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() != 0)
            {
                // A cell does not exist at the specified row.
                rows.First().Remove();
                worksheet.Save();
            }

            Row row = new Row
            {
                RowIndex = (UInt32)rowIndex
            };

            for (int i = 0; i < dataRow.Table.Columns.Count; i++)
            {
                Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i], cellStyleIndex[i], worksheet);
                row.AppendChild(dataCell);
            }

            return row;
        }
        private static Cell createTextCell(
            int columnIndex,
            int rowIndex,
            object cellValue,
            string cellStyleIndex,
            Worksheet worksheet)
        {
            uint? styleIndex = null;
            Cell cell = new Cell();

            if (_defaultStyleId != null)
            {
                cell.StyleIndex = _defaultStyleId;
            }

            if (string.IsNullOrEmpty(cellStyleIndex))
            {
                if (cellValue.GetType().Equals(typeof(int)))
                {
                    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                    cell.CellValue = new CellValue(cellValue.ToString());
                }
                else
                {
                    cell.DataType = CellValues.InlineString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    InlineString inlineString = new InlineString();
                    Text t = new Text();
                    t.Text = Convert.ToString(cellValue).Trim();
                    inlineString.AppendChild(t);
                    cell.AppendChild(inlineString);
                }
            }
            else if (cellStyleIndex.Equals(Constants.ExportExcel.CellStyleIndex[0], StringComparison.OrdinalIgnoreCase))
            {

                string data = Convert.ToString(cellValue).Trim();

                if (data.Contains("\n"))
                {
                    cell = new Cell();

                    cell.DataType = CellValues.SharedString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    cell.StyleIndex = _multilineStyleId;

                    CellValue cellValue21 = new CellValue();
                    cellValue21.Text = string.Empty + InsertSharedString(data, _sharedStringTablePart1);

                    cell.CellValue = cellValue21;
                }
                else
                {
                    cell.DataType = CellValues.InlineString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    InlineString inlineString = new InlineString();
                    Text t = new Text();
                    t.Text = data;
                    inlineString.AppendChild(t);
                    cell.AppendChild(inlineString);
                }
            }
            else if (cellStyleIndex.Equals(Constants.ExportExcel.CellStyleIndex[1], StringComparison.OrdinalIgnoreCase))
            {
                styleIndex = _dateStyleId;
                cell.StyleIndex = styleIndex.Value;
                string dateTime = string.Empty;
                if (!string.IsNullOrEmpty(cellValue.ToString()))
                {
                    dateTime = Convert.ToDateTime(cellValue).Date.ToOADate().ToString();
                }

                CellValue value = new CellValue();
                value.Text = dateTime.Replace(",", ".");
                cell.AppendChild(value);
            }
            else if (cellStyleIndex.Equals(Constants.ExportExcel.CellStyleIndex[3], StringComparison.OrdinalIgnoreCase))
            {
                string data = Convert.ToString(cellValue).Trim().Replace("\r\n", "\n").Replace("\n", "\r\n");
                if (data.Contains("\n"))
                {
                    cell = new Cell();

                    cell.DataType = CellValues.SharedString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    cell.StyleIndex = _multilineStyleId;// (UInt32Value)3U;

                    CellValue cellValue21 = new CellValue();
                    cellValue21.Text = string.Empty + InsertSharedString(data, _sharedStringTablePart1);

                    cell.CellValue = cellValue21;
                }
                else
                {
                    cell.DataType = CellValues.InlineString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    InlineString inlineString = new InlineString();
                    Text t = new Text();

                    t.Text = data;
                    inlineString.AppendChild(t);
                    cell.AppendChild(inlineString);
                }
            }
            else if (cellStyleIndex.Equals(Constants.ExportExcel.CellStyleIndex[4], StringComparison.OrdinalIgnoreCase))
            {
                cell.DataType = CellValues.InlineString;
                cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                InlineString inlineString = new InlineString();
                Text t = new Text();
                if (Convert.ToString(cellValue).Equals("0") || Convert.ToString(cellValue).Equals("1"))
                {
                    t.Text = Constants.ExportExcel.YesNo[Convert.ToUInt32(cellValue)];
                }
                else if (Convert.ToString(cellValue).Equals("true", StringComparison.InvariantCultureIgnoreCase))
                {
                    t.Text = Constants.ExportExcel.YesNo[1];
                }
                else if (Convert.ToString(cellValue).Equals("false", StringComparison.InvariantCultureIgnoreCase))
                {
                    t.Text = Constants.ExportExcel.YesNo[0];
                }
                else
                {
                    t.Text = cellValue.ToString();
                }

                inlineString.AppendChild(t);
                cell.AppendChild(inlineString);
            }
            else if (cellStyleIndex.Equals(Constants.ExportExcel.CellStyleIndex[5], StringComparison.OrdinalIgnoreCase))
            {
                string data = Convert.ToString(cellValue).Trim().Replace("\r\n", "\n").Replace("\n", "\r\n");
                data = Strip(HttpUtility.HtmlDecode(data));
                if (data.Contains("\n"))
                {
                    cell = new Cell();

                    cell.DataType = CellValues.SharedString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    cell.StyleIndex = _multilineStyleId;// (UInt32Value)3U;

                    CellValue cellValue21 = new CellValue();
                    cellValue21.Text = string.Empty + InsertSharedString(data, _sharedStringTablePart1);

                    cell.CellValue = cellValue21;
                }
                else
                {
                    cell.DataType = CellValues.InlineString;
                    cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
                    InlineString inlineString = new InlineString();
                    Text t = new Text();

                    t.Text = Strip(HttpUtility.HtmlDecode(data));
                    inlineString.AppendChild(t);
                    cell.AppendChild(inlineString);
                }
            }
            else if (cellStyleIndex.Equals(Constants.ExportExcel.CellStyleIndex[7], StringComparison.OrdinalIgnoreCase))
            {
                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                cell.StyleIndex = _curencelId;
                cell.CellValue = new CellValue(Convert.ToString(cellValue));
            }

            return cell;
        }
        public static string Strip(string text)
        {
            return Regex.Replace(text, @"<(.|\n)*?>", string.Empty);
        }

        private static int InsertSharedString(string s, SharedStringTablePart sharedStringPart)
        {
            if (sharedStringPart.SharedStringTable == null)
            {
                sharedStringPart.SharedStringTable = new SharedStringTable();
            }
            else
            {
                if (sharedStringPart.SharedStringTable.UniqueCount == null)
                {
                    sharedStringPart.SharedStringTable.UniqueCount = 0;
                }

                if (sharedStringPart.SharedStringTable.Count == null)
                {
                    sharedStringPart.SharedStringTable.Count = 0;
                }

            }


            int i = 0;
            foreach (SharedStringItem item in
                sharedStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == s)
                {
                    sharedStringPart.SharedStringTable.Count++;
                    return i;
                }

                ++i;
            }

            if (i == sharedStringPart.SharedStringTable.UniqueCount)
            {
                sharedStringPart.SharedStringTable.UniqueCount++;
                SharedStringItem sharedStringItem = new SharedStringItem();
                Text t = new Text();
                t.Text = s;
                sharedStringItem.Append(t);

                sharedStringPart.SharedStringTable.Append(sharedStringItem);
            }

            sharedStringPart.SharedStringTable.Count++;

            sharedStringPart.SharedStringTable.Save();
            return i;
        }

        /// <summary>
        /// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
        /// </summary>
        /// <param name="ds">DataSet containing the data to be written to the Excel.</param>
        /// <param name="filename">The filename (without a path) to call the new Excel file.</param>
        /// <param name="Response">HttpResponse of the current page.</param>
        /// <returns>Either a MemoryStream, or NULL if something goes wrong.</returns>
        public static MemoryStream CreateExcelDocumentAsStream(DataSet ds, string filename)
        {
            try
            {
                System.IO.MemoryStream stream = new System.IO.MemoryStream();
                using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
                {
                    WriteExcelFile(ds, document);
                }
                stream.Flush();
                stream.Position = 0;

                return stream;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return null;
            }
        }
#endif      //  End of "INCLUDE_WEB_FUNCTIONS" section

        /// <summary>
        /// Create an Excel file, and write it to a file.
        /// </summary>
        /// <param name="ds">DataSet containing the data to be written to the Excel.</param>
        /// <param name="excelFilename">Name of file to be written.</param>
        /// <returns>True if successful, false if something went wrong.</returns>
        public static bool CreateExcelDocument(DataSet ds, string excelFilename)
        {
            try
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
                {
                    WriteExcelFile(ds, document);
                }
                Trace.WriteLine("Successfully created: " + excelFilename);
                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }

        private static UInt32Value CreateFont(Stylesheet styleSheet, string fontName, double? fontSize, bool isBold)
        {
            Font font = new Font();

            if (!string.IsNullOrEmpty(fontName))
            {
                FontName name = new FontName()
                {
                    Val = fontName
                };
                font.Append(name);
            }

            if (fontSize.HasValue)
            {
                FontSize size = new FontSize()
                {
                    Val = fontSize.Value
                };
                font.Append(size);
            }

            if (isBold == true)
            {
                Bold bold = new Bold();
                font.Append(bold);
            }

            styleSheet.Fonts.Append(font);
            UInt32Value result = styleSheet.Fonts.Count;
            styleSheet.Fonts.Count++;
            return result;
        }

        private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
        {
            //  Create the Excel file contents.  function is used when creating an Excel file either writing 
            //  to a file, or writing to a MemoryStream.
            spreadsheet.AddWorkbookPart();
            spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
            spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

            uint worksheetNumber = 1;

            Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            foreach (DataTable dt in ds.Tables)
            {
                //  For each worksheet you want to create
                string worksheetName = dt.TableName;

                //  Create worksheet part, and add it to the sheets collection in workbook
                WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
                Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
                sheets.Append(sheet);

                //  Append worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
                WriteDataTableToExcelWorksheet(dt, newWorksheetPart);

                worksheetNumber++;
            }

            spreadsheet.WorkbookPart.Workbook.Save();
        }

        private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
        {
            OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
            writer.WriteStartElement(new Worksheet());
            writer.WriteStartElement(new SheetData());

            string cellValue = "";

            //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
            //
            //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
            //  cells of data, we'll know if to write Text values or Numeric cell values.
            int numberOfColumns = dt.Columns.Count;
            bool[] IsNumericColumn = new bool[numberOfColumns];

            string[] excelColumnNames = new string[numberOfColumns];
            for (int n = 0; n < numberOfColumns; n++)
                excelColumnNames[n] = GetExcelColumnName(n);

            //
            //  Create the Header row in our Excel Worksheet
            //
            uint rowIndex = 1;

            writer.WriteStartElement(new Row { RowIndex = rowIndex });
            for (int colInx = 0; colInx < numberOfColumns; colInx++)
            {
                DataColumn col = dt.Columns[colInx];
                AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
                IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
            }
            writer.WriteEndElement();   //  End of header "Row"

            //
            //  Now, step through each row of data in our DataTable...
            //
            double cellNumericValue = 0;
            foreach (DataRow dr in dt.Rows)
            {
                // ...create a new row, and append a set of row's data to it.
                ++rowIndex;

                writer.WriteStartElement(new Row { RowIndex = rowIndex });

                for (int colInx = 0; colInx < numberOfColumns; colInx++)
                {
                    cellValue = dr.ItemArray[colInx].ToString();

                    // Create cell with data
                    if (IsNumericColumn[colInx])
                    {
                        //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                        //  If numeric value is NULL, then don't write anything to the Excel file.
                        cellNumericValue = 0;
                        if (double.TryParse(cellValue, out cellNumericValue))
                        {
                            cellValue = cellNumericValue.ToString();
                            AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                        }
                    }
                    else
                    {
                        //  For text cells, just write the input data straight out to the Excel file.
                        AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                    }
                }
                writer.WriteEndElement(); //  End of Row
            }
            writer.WriteEndElement(); //  End of SheetData
            writer.WriteEndElement(); //  End of worksheet

            writer.Close();
        }

        private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
        {
            //  Add a new Excel Cell to our Row 
            writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
        }

        private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
        {
            //  Add a new Excel Cell to our Row 
            writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number });
        }

        private static UInt32Value createCellFormat(
          Stylesheet styleSheet,
          UInt32Value fontIndex,
          UInt32Value fillIndex,
          UInt32Value numberFormatId)
        {
            CellFormat cellFormat = new CellFormat();

            if (fontIndex != null)
                cellFormat.FontId = fontIndex;

            if (fillIndex != null)
                cellFormat.FillId = fillIndex;

            if (numberFormatId != null)
            {
                cellFormat.NumberFormatId = numberFormatId;
                cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            }

            cellFormat.BorderId = _borderID;
            styleSheet.CellFormats.Append(cellFormat);
            var alignment = new Alignment() { WrapText = true };
            cellFormat.Append(alignment);
            UInt32Value result = styleSheet.CellFormats.Count;
            styleSheet.CellFormats.Count++;
            return result;
        }

        private static string GetExcelColumnName(int columnIndex)
        {
            //  Convert a zero-based column index into an Excel column reference  (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
            //
            //  eg  GetExcelColumnName(0) should return "A"
            //      GetExcelColumnName(1) should return "B"
            //      GetExcelColumnName(25) should return "Z"
            //      GetExcelColumnName(26) should return "AA"
            //      GetExcelColumnName(27) should return "AB"
            //      ..etc..
            //
            if (columnIndex < 26)
                return ((char)('A' + columnIndex)).ToString();

            char firstChar = (char)('A' + (columnIndex / 26) - 1);
            char secondChar = (char)('A' + (columnIndex % 26));

            return string.Format("{0}{1}", firstChar, secondChar);
        }

        private Cell createValueCell(int columnIndex, int rowIndex, object cellValue, Nullable<uint> styleIndex)
        {
            Cell cell = new Cell();
            cell.CellReference = GetExcelColumnName(columnIndex) + rowIndex;
            CellValue value = new CellValue();
            value.Text = cellValue.ToString();

            //apply the cell style if supplied
            if (styleIndex.HasValue)
                cell.StyleIndex = styleIndex.Value;

            cell.AppendChild(value);

            return cell;
        }
    }
}
